﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RN.ERP.Entity.QD;
using RN.ERP.Entity.ResultModels;

namespace RN.ERP.DAL.DAL
{
    public class PersonDAL
    {
        private QD_Context qdcontext = new QD_Context();

        #region 人员信息 张剑
        /// <summary>
        /// 获取企业下的普通工人信息列表
        /// </summary>
        /// <param name="corpUserGuid">企业Guid</param>
        /// <param name="condition">sql where</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageSize">条数</param>
        /// <returns></returns>
        public List<tb_Emp_Manager1> GetPersonEmployees(string corpUserGuid, string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<tb_Emp_Manager1>();

            string sql = @"select * from tb_Emp_Manager where  (IsDel <> 1 or IsDel is null) and (IsExit <>1 or IsExit is null) and CorpGuid='" + corpUserGuid + "' " + (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            count = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(sql).ToList().Count();
            list = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(sql).OrderByDescending(m => m.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        public List<tb_Emp_Manager1> GetPersonManagersByCorpUserGuid(string corpUserGuid, string condition, int pageIndex, int pageSize, out int count, string type)
        {
            var list = new List<tb_Emp_Manager1>();

            StringBuilder strsql = new StringBuilder();
            switch (type)
            {
                case "0"://项目主管、经办人
                    strsql.Append("select m.ID, m.EmpGuid,m.Name,m.TypeFlag,m.Sex,m.corpName,m.IDCard,m.Tel,m.FlowCode,m.Description ");
                    strsql.Append(" FROM tb_Emp_Manager m where m.TypeFlag in ('100','101') and m.CorpGuid='" + corpUserGuid + "' AND m.IsDel=0 ");
                    strsql.Append(" and m.TypeFlag IS NOT NULL ORDER BY m.ID desc");
                    break;
                case "1"://普通招标代理人员
                    strsql.Append("SELECT DISTINCT m.ID, m.EmpGuid,m.Name,m.TypeFlag,m.Sex,m.corpName,m.IDCard,m.Tel,m.FlowCode,m.Description ");
                    strsql.Append(" FROM tb_Emp_Manager m INNER JOIN tb_emp_titledetails e ON m.EmpGuid=e.EmpGuid ");
                    strsql.Append(" WHERE m.CorpGuid='" + corpUserGuid + "' AND m.IsDel=0 AND e.IsDel=0 ");
                    strsql.Append(" and m.TypeFlag ='103'  AND e.TypeFlags =103");
                    strsql.Append(" ORDER by m.ID desc");
                    break;
                case "2"://注册类人员
                    strsql.Append("SELECT DISTINCT m.ID, m.EmpGuid,m.Name,m.TypeFlag,m.Sex,m.corpName,m.IDCard,m.Tel,m.FlowCode,m.Description ");
                    strsql.Append(" FROM tb_Emp_Manager m INNER JOIN tb_emp_titledetails e ON m.EmpGuid=e.EmpGuid ");
                    strsql.Append(" WHERE m.CorpGuid='" + corpUserGuid + "' AND m.IsDel=0 AND e.IsDel=0 ");
                    strsql.Append(" AND m.TypeFlag IS NOT NULL and m.TypeFlag NOT in ( '101','100')  AND e.TypeFlags in (71,72,73,74,75,76,77)");
                    strsql.Append(" ORDER by m.ID desc");
                    break;
                case "3"://职称类人员
                    strsql.Append("select m.ID, m.EmpGuid,m.Name,m.TypeFlag,m.Sex,m.corpName,m.IDCard,m.Tel,m.FlowCode,m.Description ");
                    strsql.Append(" from tb_Emp_Manager m  where m.CorpGuid='" + corpUserGuid + "' AND m.IsDel=0");
                    strsql.Append(" and m.TypeFlag IS NOT NULL and m.TypeFlag NOT in ( '101','100')");
                    strsql.Append(" AND m.DutyLevel IN ('初级','1','中级','2','高级','3') ORDER BY m.ID desc");
                    break;
                default:
                    break;
            }
            var personlist = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(strsql.ToString()).ToList();
            count = personlist.Count();
            list = personlist.OrderByDescending(m => m.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 获取企业下的普通工人信息列表总记录数
        /// </summary>
        /// <param name="corpUserGuid">企业Guid</param>
        /// <param name="condition">sql where</param>
        /// <returns></returns>
        public int GetPersonEmployeesCount(string corpUserGuid, string condition)
        {
            int count = 0;
            string sql = @"SELECT count(1) FROM [dbo].tb_Emp_Manager  left join tb_emp_ManagerRole
on tb_Emp_Manager.empguid=tb_emp_ManagerRole.empguid where (tb_Emp_Manager.IsDel <> 1   or  tb_Emp_Manager.isdel is null)  and (tb_Emp_Manager.IsExit <>1 or tb_Emp_Manager.IsExit is null) and  tb_Emp_Manager.CorpGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            count = qdcontext.Database.SqlQuery<int>(sql).FirstOrDefault();
            return count;
        }

        /// <summary>
        /// 获取企业下的指定工人的证书列表
        /// </summary>
        /// <param name="empGuid">empGuid</param>
        /// <param name="condition">sql where</param>
        /// <returns></returns>
        public List<Tb_Emp_TitleDetails> GetPersonTitleDetails(string empGuid, string condition)
        {
            var list = new List<Tb_Emp_TitleDetails>();
            string sql = string.Format("select * from tb_emp_titledetails  where (IsDel <> 1  or IsDel is null)  and empguid = '{0}' ", empGuid) + (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = qdcontext.Database.SqlQuery<Tb_Emp_TitleDetails>(sql).ToList();
            return list;
        }

        #endregion

        #region 人员信息 赵娇娇 、
        /// <summary>
        /// 获取企业下的管理人员信息。
        /// </summary>
        /// <param name="corpUserGuid">企业编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Employee> GetPersonManagers(string corpUserGuid, string condition)
        {
            var list = new List<tb_Emp_Employee>();

            string sql = "SELECT * FROM [dbo].tb_Emp_Employee where typeflag>-1 and flowcode like '1%' and CorpUserGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = qdcontext.Database.SqlQuery<tb_Emp_Employee>(sql).ToList();

            return list;

        }


        /// <summary>
        /// 获取企业下的普通工人信息。
        /// </summary>
        /// <param name="corpUserGuid">企业编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager> GetPersonEmployee(string corpUserGuid, string condition)
        {
            var list = new List<tb_Emp_Manager>();

            string sql = "SELECT * FROM [dbo].tb_Emp_Manager where (IsDel <> 1 or IsDel is null)  and (IsExit <> 1 or IsExit is null) and CorpGuid='" + corpUserGuid + "' " +
            (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            if (corpUserGuid == "")
            {
                sql = "select * from tb_Emp_Manager where (IsDel <> 1 or IsDel is null)  and (IsExit <> 1 or IsExit is null) " + condition;
            }
            list = qdcontext.Database.SqlQuery<tb_Emp_Manager>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 获取企业人员列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public IList<tb_Emp_Manager1> GetEmpManagerInfoList(int pageCount, int pageSize, string conditions, string systemid, out int count)
        {
            string strwhere = string.Empty;
            if (systemid == "2")
            {
                strwhere = "SELECT distinct m.*,e.Year_Sorce as Score FROM dbo.tb_Emp_Manager m LEFT JOIN dbo.e_SorcePublish_2006 e"
                            + " ON m.ID=e.KindID LEFT JOIN dbo.t_sys_users u ON m.CorpGuid=u.UserGuid "
                            + " where    u.bGrade in (105,99105)   and (m.IsDel <> 1 or m.IsDel is null) and  (m.IsExit <> 1 or m.IsExit is null) " + conditions + " order by Score desc";
            }
            else
            {
                strwhere = "select * from tb_Emp_Manager m  left join t_Sys_Users u on u.UserGuid = m.CorpGuid where (m.IsDel <> 1 or m.IsDel is null)  " + conditions + " order by m.corpname desc";
            }
            //IList<tb_Emp_Manager> managerlist = qdcontext.Database.SqlQuery<tb_Emp_Manager>(strwhere)
            //                  .Take(pageSize + (pageCount) * pageSize)
            //                  .Skip(pageSize + (pageCount - 1) * pageSize).ToList();
            IList<tb_Emp_Manager1> managerlist = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(strwhere).Distinct().ToList();
            count = managerlist.Count;
            managerlist = managerlist.Skip((pageCount - 1) * pageSize).Take(pageSize).Distinct().ToList();
            return managerlist;
        }

        /// <summary>
        /// 获取注册类企业人员列表
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageSize"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public IList<tb_Emp_Manager1> GetEmpManagerInfoListReg(int pageCount, int pageSize, string conditions, string systemid, out int count)
        {
            string strwhere = string.Empty;
            if (systemid == "2")
            {
                strwhere = "SELECT distinct m.*,e.Year_Sorce as Score,t.typeflags FROM dbo.tb_Emp_Manager m LEFT JOIN dbo.e_SorcePublish_2006 e"
                            + " ON m.ID=e.KindID LEFT JOIN dbo.t_sys_users u ON m.CorpGuid=u.UserGuid LEFT JOIN tb_emp_titleDetails t ON m.empguid=t.empguid"
                            + " where    u.bGrade in (105,99105)   and (m.IsDel <> 1 or m.IsDel is null) and  (m.IsExit <> 1 or m.IsExit is null) " + conditions + " order by Score desc";
            }
            else
            {
                strwhere = "select * from tb_Emp_Manager m  left join t_Sys_Users u on u.UserGuid = m.CorpGuid where (m.IsDel <> 1 or m.IsDel is null)  " + conditions + " order by m.corpname desc";
            }

            IList<tb_Emp_Manager1> managerlist = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(strwhere).Distinct().ToList();
            count = managerlist.Count;
            managerlist = managerlist.Skip((pageCount - 1) * pageSize).Take(pageSize).Distinct().ToList();
            return managerlist;
        }

        /// <summary>
        /// 获取人员列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int GetEmpManagerListCount(string condition)
        {
            string sql = @"SELECT count(*) From tb_Emp_Manager m left join t_Sys_Users u on u.UserGuid = m.CorpGuid
                             where (m.IsDel <> 1  or m.IsDel is null)   and (m.IsExit <> 1 or m.IsExit is null) ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);

            int num = qdcontext.Database.SqlQuery<int>(sql).FirstOrDefault();
            return num;
        }
        /// <summary>
        /// 获取证书ID对应人员信息。
        /// </summary>
        /// <param name="qualityId">证书ID。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager> GetPersonEmployeeByQualityId(string qualityId)
        {
            var list = new List<tb_Emp_Manager>();

            string sql = "SELECT * FROM [dbo].tb_Emp_Manager where exists (select 1 from tb_emp_titledetails where id=" + qualityId + " and empguid=tb_emp_manager.empguid)";
            list = qdcontext.Database.SqlQuery<tb_Emp_Manager>(sql).ToList();
            return list;
        }

        /// <summary>
        /// 获取B证ID对应人员信息。
        /// </summary>
        /// <param name="qualityBId"B郑ID编号。</param>
        /// <returns></returns>
        public List<tb_Emp_Manager> GetPersonEmployeeByQualityBId(string qualityBId)
        {
            var list = new List<tb_Emp_Manager>();
            int id = 0;
            Guid guid = new Guid();
            string sql = "";
            if (int.TryParse(qualityBId, out id))
            {
                sql = "SELECT * FROM [dbo].tb_Emp_Manager where exists (select 1 from tb_emp_titledetailsExt where id=" + qualityBId + " and empguid=tb_emp_manager.empguid)";

            }
            else if (Guid.TryParse(qualityBId, out guid))
            {
                sql = "SELECT * FROM [dbo].tb_Emp_Manager where exists (select 1 from tb_emp_titledetailsExt where empguid='" + qualityBId + "' and empguid=tb_emp_manager.empguid)";
            }
            list = qdcontext.Database.SqlQuery<tb_Emp_Manager>(sql).ToList();
            return list;
        }

        public Tb_Emp_TitleDetailsExt GetPersonBQualityById(string personId)
        {
            string strSql = "SELECT * FROM dbo.tb_Emp_TitleDetailsExt WHERE EXISTS( SELECT 1 FROM dbo.tb_Emp_Manager WHERE ID=@id AND EmpGuid=tb_Emp_TitleDetailsExt.EmpGuid)";
            IList<Tb_Emp_TitleDetailsExt> b = qdcontext.Database.SqlQuery<Tb_Emp_TitleDetailsExt>(strSql, new SqlParameter("@id", personId)).ToList();
            if (b.Count > 0)
            {
                return b[0];
            }
            else
            {
                return null;
            }
        }

        //        /// <summary>
        //        /// 获取人员列表
        //        /// </summary>
        //        /// <param name="pageIndex"></param>
        //        /// <param name="pageSize"></param>
        //        /// <param name="condition"></param>
        //        /// <returns></returns>
        //        public IList<tb_Emp_Manager1> GetEmpManagerList(int pageIndex, int pageSize, string condition)
        //        {
        //            var list = new List<tb_Emp_Manager1>();
        //            string sql = @"SELECT Tb_FlowCode.flowcode as flowcode1, * From tb_Emp_Manager 
        //                             left join Tb_FlowCode  On CONVERT(nvarchar(50),tb_Emp_Manager.EmpGuid) =Tb_FlowCode.ObjectId
        //                             where Tb_FlowCode.CodeType = '8000' AND  Tb_FlowCode.FlowCode <>'0'";
        //            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
        //            sql += " order by tb_Emp_Manager.id desc";
        //            list = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(sql).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
        //            return list;
        //        }



        /// <summary>
        /// 查询考核结果
        /// </summary>
        /// <param name="IDCard"></param>
        /// <param name="TypeFlag"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public IList<e_SorcePublish_2006> GetSorcePublishList(int pageIndex, int pageSize, string condition, out int counts)
        {
            var list = new List<e_SorcePublish_2006>();
            string sql = @"SELECT A.* FROM e_SorcePublish_2006 A 
                   left join t_Sys_Users C on A.TreeId = C.DogId 
                   left join tb_Corp_CompanyInfo D on C.UserGuid = D.CorpUserGuid
                   left join a_CropKind E on A.KindId = E.Id
                   WHERE 1=1 ";
            sql += (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            list = qdcontext.Database.SqlQuery<e_SorcePublish_2006>(sql).ToList();
            var listpage = list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            counts = list.Count;
            return listpage;
        }

        /// <summary>
        /// 查询考核结果
        /// </summary>
        /// <param name="IDCard"></param>
        /// <param name="TypeFlag"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public IList<e_SorcePublish_2006> GetSorcePublishLists(DTO.SorcePublishCondition condition, out int counts)
        {
            var list = new List<e_SorcePublish_2006>();
            string sql = @"SELECT A.* FROM e_SorcePublish_2006 A 
                   left join t_Sys_Users C on A.TreeId = C.DogId 
                   left join tb_Corp_CompanyInfo D on C.UserGuid = D.CorpUserGuid
                   left join a_CropKind E on A.KindId = E.Id
                   WHERE 1=1 ";

            //0本地企业,1外地企业
            //if (condition.Area != null && condition.Area == "1" && condition.bFlag != null && (condition.bFlag == 11 || condition.bFlag == 1))//施工外地企业17
            //{
            //    sql += String.Format(" and c.bgrade={0} and (d.flowcode like '{1}%' or d.bcanbid like '%{2}%') ", condition.bGrade, condition.FlowCode, condition.bCanBid);
            //}
            //else
            //{
            Guid Corpguid = new Guid();
            if (condition.CorpUserGuid != null && Guid.TryParse(condition.CorpUserGuid.ToString(), out Corpguid))
            {
                sql += " and c.userguid='" + condition.CorpUserGuid + "' ";
            }
            if (condition.tb_Corp_IsOuter != null)
            {
                sql += " and D.TB_CORP_ISOUTER =" + condition.tb_Corp_IsOuter + " ";
            }
            if (!string.IsNullOrEmpty(condition.bGrade))
            {
                sql += " and c.bGrade  in (" + condition.bGrade + ") ";
            }
            if (condition.classID != null)
            {
                sql += " and D.classID<> " + condition.classID + " ";
            }
            //sql += " and d.flowcode like '1%' ";
            //sql += String.Format(" and D.TB_CORP_ISOUTER ={0} and c.bGrade  in  ({1}) and D.classID<>{2}  and d.flowcode like '{3}%' ", condition.tb_Corp_IsOuter, condition.bGrade, condition.classID, condition.FlowCode);

            //if (condition.bFlag != null && (condition.bFlag == 11 || condition.bFlag == 12))
            //{
            //    sql += " and e.isKh= " + condition.isKH;
            //}

            if (!string.IsNullOrEmpty(condition.CorpName))
            {
                sql += String.Format(" and A.corpName like '%{0}%' ", condition.CorpName);
            }
            if (!string.IsNullOrEmpty(condition.PersonName))
            {
                sql += String.Format(" and A.PersonName like '%{0}%' ", condition.PersonName);
            }
            if (condition.KindID != null)
            {
                sql += " and A.KindID =" + condition.KindID;
            }

            if (condition.bFlag != null)
            {
                if (!string.IsNullOrEmpty(condition.bFlag))
                {
                    sql += String.Format(" and A.bFlag  in ( {0}) and D.classid <> {1}", condition.bFlag, condition.classID);
                }
                else
                {
                    condition.classID = 2507;
                    sql += " and D.classid = " + condition.classID;//劳务分包
                }
            }

            sql += " order by A.Year_Sorce desc, (case A.LevelTitle when '特级' then 1 when '壹级' then 2 when '贰级' then 3 when '叁级' then 4 when '甲级' then 5 when '乙级' then 6 when '丙级' then 7 else 99 end) asc ";

            list = qdcontext.Database.SqlQuery<e_SorcePublish_2006>(sql).ToList();
            var listpage = list.Skip((condition.pageIndex - 1) * condition.pageSize).Take(condition.pageSize).ToList();
            counts = list.Count;
            return listpage;
        }

        public List<DTO.tbCorpList> GetCorpList(List<DTO.tbCorpList> list)
        {
            var newlist = new List<DTO.tbCorpList>();
            string corpUserGuid = string.Empty;
            foreach (var item in list)
            {
                corpUserGuid += "'" + item.corpuserguid.ToString() + "',";
            }
            corpUserGuid = corpUserGuid.TrimEnd(',');
            string sql = @"SELECT tc.* 
                            FROM tb_Corp_CompanyInfo tc
                            left JOIN tb_Corp_Quality tq
                            on tc.CorpUserGuid = tq.CorpUserGuid and tq.kindid = 132 AND tq.IsDel = 0
                            where tc.CorpUserGuid IN (" + corpUserGuid + ") " +
                            "ORDER BY (case tq.GradeName when '甲级' then 1 when '乙级' then 2 when '暂定' then 3 when '不分等级' then 4  else 99 end) asc ";

            var corplist = qdcontext.Database.SqlQuery<Tb_Corp_CompanyInfo>(sql).ToList();

            foreach (var item in corplist)
            {
                var tbc = new DTO.tbCorpList();
                tbc.corpuserguid = item.CorpUserGuid;
                tbc.corpname = item.CorpName;
                newlist.Add(tbc);

            }
            return newlist;
        }

        public IList<tb_Emp_Employee> GetEnterpriseEmployeeList(string typeFlags, string corpUserGuid)
        {

            var list = new List<tb_Emp_Employee>();
            string sql = @"SELECT * From tb_emp_employee 
                           where TypeFlag IN (" + typeFlags + ") AND CorpUserGuid = '" + corpUserGuid + "'";
            list = qdcontext.Database.SqlQuery<tb_Emp_Employee>(sql).ToList();

            return list;
        }

        public IList<tb_Emp_Employee> GetEmployeeInfoFlowList(string corpUserGuid, int type, string flowcode)
        {
            string typeflags = "  TypeFlag >0  and  TypeFlag IN (3,4,5,7,8,9)";
            if (flowcode != null && (!flowcode.StartsWith("1") && flowcode.IndexOf("021") < 0 && flowcode.StartsWith("0,019")))
            {
                typeflags = "TypeFlag IN (-3,-4,-5,-7,-8,-9)";
            }
            if (type == 1)
            {
                typeflags = " TypeFlag >0  and TypeFlag IN (3,4,5,7,8,9,6,11,15,10)";
                if ((!flowcode.StartsWith("1") && flowcode.IndexOf("021") < 0 && flowcode.StartsWith("0,019")))
                {
                    typeflags = "TypeFlag IN (-3,-4,-5,-7,-8,-9,-6,-11,-15,-10)";
                }
            }
            if (true)
            {

            }
            var list = new List<tb_Emp_Employee>();
            string sql = @"SELECT Tb_FlowCode.FlowCode ,EmpGuid
                          ,CorpUserGuid,tb_emp_employee.ID,TypeFlag
                          ,Name,Sex,JobAS,TitleType
                          ,TitleCode,Education,Speciality
                          ,Job ,SumTime ,Resume
                          ,Ispass ,DoMajor,AlterDateTime
                          ,AlterForwardID,CheckComment,AqzsCode
                          ,AqzsGetDate,BrithDay,MinZu
                          ,JiGuan,Certificatid,zgzcCode
                          ,DogID,tel,mobile,Profession
                          ,StartYear,oldid,GraduatedSchool
                          ,CZNum,CZRatio,ZJCertificateCode,IsBuLu
                          ,AQKHFZDW,AqzsEndDate ,IDCardAddress
                          ,ACode,tb_emp_employee.Description From tb_emp_employee 
                           left join Tb_FlowCode On CONVERT(nvarchar(50),tb_emp_employee.CorpUserGuid) =Tb_FlowCode.ObjectId
                           where Tb_FlowCode.CodeType = '8001' AND " + typeflags + " AND Tb_FlowCode.ObjectId = '" + corpUserGuid + "'";
            list = qdcontext.Database.SqlQuery<tb_Emp_Employee>(sql).ToList();

            return list;
        }

        /// <summary>
        /// 获取项目库押证数量
        /// </summary>
        /// <param name="managerId"></param>
        /// <returns>int</returns>
        public int CheckBzkCounts(int managerId)
        {
            int bzkCounts = 0;
            string sql = String.Format(@"SELECT COUNT(1) FROM  h_ProjectManager WHERE ManagerId={0} 
                                         AND EXISTS (SELECT 1 FROM h_projectBzk_New p INNER JOIN 
                                         h_ProjectCorp c ON p.ID=c.ProjectId 
                                         WHERE c.Id=h_ProjectManager.ProjectCorpId AND p.bStatus<>255 AND p.IsDel=0 AND c.IsDel=0)", managerId);

            bzkCounts = qdcontext.Database.SqlQuery<int>(sql).FirstOrDefault();
            return bzkCounts;
        }
        public IList<string> CheckBzkTitle(int managerId)
        {
            string sql = String.Format(@"SELECT  p.Title FROM      h_projectBzk_New AS p INNER JOIN
                                        h_ProjectCorp AS c ON p.ID = c.ProjectId INNER JOIN h_ProjectManager m ON c.Id = m.ProjectCorpId
                                        WHERE   (p.bStatus <> 255) AND (p.IsDel = 0) AND (c.IsDel = 0) and  m.ManagerId={0} ", managerId);
            return qdcontext.Database.SqlQuery<string>(sql).ToList();

        }
        #endregion

        #region 人员转入转出查询
        /// <summary>
        /// 查询转入接收人员列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<V_PersonToEntryList> GetAdminPersonToEntryList(string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<V_PersonToEntryList>();

            string sql = String.Format("select * from V_PersonToEntryList where  1 = 1 {0}", (string.IsNullOrEmpty(condition) ? string.Empty : condition));
            count = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).ToList().Count();
            list = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).OrderByDescending(m => m.csid).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        ///  查询转出人员列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<V_PersonToEntryList> GetAdminPersonToExitList(string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<V_PersonToEntryList>();
            string sql = @" SELECT  cs.Id as csid, cs.ObjectName as BeforeChange, '' as BeforeValue, '' as AfterChange, '' as AfterValue, fc.FlowCode as EFlowCode, tsys.RoleID, emp.*
		                    FROM Tb_ChangeSetRecord cs LEFT JOIN tb_Emp_Manager emp
		                    ON cs.ObjectId = emp.EmpGuid LEFT JOIN Tb_FlowCode fc
                            ON cs.Id = fc.ObjectId  LEFT JOIN t_Sys_Users tsys
                            ON tsys.UserGuid = emp.CorpGuid
                            WHERE cs.ObjectType = '9' AND fc.CodeType = 8009 AND fc.IsDel = 0 
		                    AND (emp.IsDel <> 1 or emp.IsDel is null) " + (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            count = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).ToList().Count();
            list = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).OrderByDescending(m => m.csid).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 获取企业下的需转出员工信息列表
        /// </summary>
        /// <param name="corpUserGuid"></param>
        /// <param name="condition"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<tb_Emp_Manager1> GetPersonWantToExit(string corpUserGuid, string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<tb_Emp_Manager1>();

            string sql = String.Format("select * from tb_Emp_Manager where  (IsDel <> 1 or IsDel is null)  and CorpGuid = '{0}' {1}", corpUserGuid, (string.IsNullOrEmpty(condition) ? string.Empty : condition));
            count = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(sql).ToList().Count();
            list = qdcontext.Database.SqlQuery<tb_Emp_Manager1>(sql).OrderByDescending(m => m.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }

        /// <summary>
        /// 获取待转区信息列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<V_PersonToEntryList> GetPersonToWaitArea(string condition, int pageIndex, int pageSize, out int count)
        {
            var list = new List<V_PersonToEntryList>();

            string sql = @"SELECT  cs.Id as csid, cr.BeforeChange,cr.BeforeValue, cr.AfterChange,cr.AfterValue, '0' as EFlowCode, tsys.RoleID, emp.*
		                   FROM Tb_ChangeSetRecord cs LEFT JOIN tb_ChangeRecord cr
		                   ON cs.Id = cr.ChangeSetID LEFT JOIN tb_Emp_Manager emp
		                   ON cr.EmpGuid = emp.EmpGuid LEFT JOIN t_Sys_Users tsys
                           ON tsys.UserGuid = emp.CorpGuid
		                   WHERE cs.ObjectType = '8' AND cs.IsDel = 0 AND cr.ClassID = 8008
		                   AND cr.AfterValue = '' AND emp.IsExit = 1 AND (emp.IsDel <> 1 or emp.IsDel is null) " + (string.IsNullOrEmpty(condition) ? string.Empty : condition);
            count = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).ToList().Count();
            list = qdcontext.Database.SqlQuery<V_PersonToEntryList>(sql).OrderByDescending(m => m.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            return list;
        }
        #endregion
    }
}
